Create Calculated Columns and Measures 9



 Create Measures and Calculated Columns

ยท         MEASURE AND COLUMN CALCULATION

                DAX Calculation for measure

  1.             TotalticketsNoDeleted = [TotalTickets]- [Deleted Tickets]

2.    TotalTickets = DISTINCTCOUNT(Fact_Zendesk [TicketID])

3.    Resolved Tickets = CALCULATE([TotalTickets], Dim_Status[StatusKey]=1)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=2)

4.    OpenTickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=3)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=4)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=6)

5.    MTD 1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))

6.    LM_Tickets = TOTALMTD([TotalticketsNoDeleted],DATEADD('Date'[Date],-1,MONTH))

7.    LM_RollingResolved = CALCULATE([Resolved Tickets],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,DAY))

8.    LM_Resolved = TOTALMTD([Resolved Tickets],DATEADD('Date'[Date],-1,MONTH))

9.    FirstContactRate = AVERAGE(Fact_Zendesk[FirstContactResolutionRate])

10. Deleted Tickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=5)

11. CurrnetMonth_Tickets = TOTALMTD([TotalticketsNoDeleted],'Date'[Date])

12. CurrnetMonth_Resolved = TOTALMTD([Resolved Tickets],'Date'[Date])

13. CurMonth1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))

14. AvgTime to close = AVERAGE(Fact_Zendesk[TimeToCloseDays])

15. AvgClosingDays = AVERAGE(Fact_Zendesk[TimeToCloseDays])

16. .YTD AvgReplayTime = TOTALYTD([Avg_Res_Time],'Date'[Date])

17. .SamePeriodAVGReplaytime = CALCULATE([Avg_Res_Time],SAMEPERIODLASTYEAR('Date'[Date]))

18. .PrevM_TimetocloseDays = CALCULATE([AvgClosingDays],DATEADD('Date'[Date],-1,MONTH))

19. .MTD_TimetocloseDays = CALCULATE([AvgTime to close],DATESMTD('Date'[Date]))

20. .MTD_FirstContactRate = CALCULATE([FirstContactRate],DATESMTD('Date'[Date]))

21. .CurM_resolved% = [CurrnetMonth_Resolved]/[CurrnetMonth_Tickets]


-       DAX for calculated column 

1.  DayofWeek = FORMAT(Dim_Ticket[Created],"dddd")

2.  FirstRes = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[RspDate],HOUR)

3.  Month_shortCC = LEFT(Dim_Ticket[Month],3)

4.  CreatedHours = HOUR(Dim_Ticket[Created])

5.  CloseTime = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[Updated],HOUR)







Create Calculated Columns and Measures 9



 Create Measures and Calculated Columns

ยท         MEASURE AND COLUMN CALCULATION

                DAX Calculation for measure

  1.             TotalticketsNoDeleted = [TotalTickets]- [Deleted Tickets]

2.    TotalTickets = DISTINCTCOUNT(Fact_Zendesk [TicketID])

3.    Resolved Tickets = CALCULATE([TotalTickets], Dim_Status[StatusKey]=1)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=2)

4.    OpenTickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=3)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=4)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=6)

5.    MTD 1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))

6.    LM_Tickets = TOTALMTD([TotalticketsNoDeleted],DATEADD('Date'[Date],-1,MONTH))

7.    LM_RollingResolved = CALCULATE([Resolved Tickets],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,DAY))

8.    LM_Resolved = TOTALMTD([Resolved Tickets],DATEADD('Date'[Date],-1,MONTH))

9.    FirstContactRate = AVERAGE(Fact_Zendesk[FirstContactResolutionRate])

10. Deleted Tickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=5)

11. CurrnetMonth_Tickets = TOTALMTD([TotalticketsNoDeleted],'Date'[Date])

12. CurrnetMonth_Resolved = TOTALMTD([Resolved Tickets],'Date'[Date])

13. CurMonth1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))

14. AvgTime to close = AVERAGE(Fact_Zendesk[TimeToCloseDays])

15. AvgClosingDays = AVERAGE(Fact_Zendesk[TimeToCloseDays])

16. .YTD AvgReplayTime = TOTALYTD([Avg_Res_Time],'Date'[Date])

17. .SamePeriodAVGReplaytime = CALCULATE([Avg_Res_Time],SAMEPERIODLASTYEAR('Date'[Date]))

18. .PrevM_TimetocloseDays = CALCULATE([AvgClosingDays],DATEADD('Date'[Date],-1,MONTH))

19. .MTD_TimetocloseDays = CALCULATE([AvgTime to close],DATESMTD('Date'[Date]))

20. .MTD_FirstContactRate = CALCULATE([FirstContactRate],DATESMTD('Date'[Date]))

21. .CurM_resolved% = [CurrnetMonth_Resolved]/[CurrnetMonth_Tickets]


-       DAX for calculated column 

1.  DayofWeek = FORMAT(Dim_Ticket[Created],"dddd")

2.  FirstRes = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[RspDate],HOUR)

3.  Month_shortCC = LEFT(Dim_Ticket[Month],3)

4.  CreatedHours = HOUR(Dim_Ticket[Created])

5.  CloseTime = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[Updated],HOUR)